package com.briup.ch01;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;

/**
 * @author yuxi
 * @version 1.0
 * @date 2025/11/5
 * @Description:
 */
public class DQLTest {
    //连接4要素
    private String driverClass="com.mysql.cj.jdbc.Driver";
    private String url="jdbc:mysql://localhost:3306/briup";
    private String username="briup";
    private String password="briup";
    //资源对象
    Connection conn=null;
    Statement stmt=null;
    ResultSet rs=null;

    //设置连接对象，SQL操作对象
    @Before
    public void canDo() {
        try {
            //1.类加载驱动
            Class.forName(driverClass);
            //2.获取连接对象
            conn=DriverManager.getConnection(url,username,password);
            //3.获取SQL操作对象
            stmt=conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //关闭资源
    @After
    public void close() {
        try {
            if(rs!=null) rs.close();
            if(stmt!=null) stmt.close();
            if(conn!=null) conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //查询操作
    @Test
    public void query() {
        try {
            //4.执行SQL语句
            String sql="select * from user";
            rs=stmt.executeQuery(sql);
            //5.处理结果集
            while(rs.next()) {
                int id=rs.getInt("id");
                String name=rs.getString("name");
                int age=rs.getInt("age");
                System.out.println("id="+id+",name="+name+",age="+age);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //登录验证
    public boolean login(String username,String passwd) {
        //1.验证数据的合法性
        if(username==null || username.trim().equals("")) {
            System.out.println("用户名不能为空！");
            return false;
        }
        if(passwd==null || passwd.trim().equals("")) {
            System.out.println("密码不能为空！");
            return false;
        }
        //2.业务处理
        try {
            String sql="select count(*) from user where name='"+username+"' and password='"+passwd+"'";
            System.out.println("sql="+sql);
            this.rs=this.stmt.executeQuery(sql);
            while(rs.next()){
                int count=rs.getInt(1);
                if(count==1){
                    System.out.println("登录成功！");
                    return true;
                }
            }
            System.out.println("登录失败！");
            sql="select count(*) from user where name='"+username+"'";
            this.rs=this.stmt.executeQuery(sql);
            while(rs.next()){
                int count=rs.getInt(1);
                if(count==0){
                    System.out.println("用户名不存在！");
                }
            }
            System.out.println("密码错误！");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return false;
    }
    //测试登录
    @Test
    public void testLogin() {
        //1.测试不合法
        this.login("","admin");
        this.login("admin","");
        //2.测试合法，用户名和密码都对
        this.login("admin","admin");
        //3.测试合法，用户名存在，密码错误
        this.login("admin","123456");
        //4.测试合法，用户名不存在
        this.login("admin1","admin");

    }
}
